
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/13/2014 23:49:19
-- Generated from EDMX file: C:\Users\syakimchik\Documents\Visual Studio 2012\Projects\ConsoleApplication1\ConsoleApplication1\Database\ClientDatabase.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [s12-02];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'EmployeeInfoSet'
CREATE TABLE [dbo].[EmployeeInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [SecondName] nvarchar(max)  NOT NULL,
    [PassportInfo_Id] int  NOT NULL,
    [FamilyComp_Id] int  NOT NULL,
    [MilitaryRecords_Id] int  NOT NULL,
    [EmploymentHistoryInfo_Id] int  NOT NULL,
    [VacationInfo_Id] int  NOT NULL,
    [ContactInfo_Id] int  NOT NULL,
    [HospitalInfo_Id] int  NOT NULL,
    [ContractsInfo_Id] int  NOT NULL
);
GO

-- Creating table 'PassportInfoSet'
CREATE TABLE [dbo].[PassportInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [Range] nvarchar(max)  NOT NULL,
    [IdentificationNumber] nvarchar(max)  NOT NULL,
    [DateOfBirth] nvarchar(max)  NOT NULL,
    [PlaceOfRegistration] nvarchar(max)  NOT NULL,
    [Sex] nvarchar(max)  NOT NULL,
    [NatalPlace] nvarchar(max)  NOT NULL,
    [Nationality] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ContactInfoSet'
CREATE TABLE [dbo].[ContactInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [HomePhoneNumber] nvarchar(max)  NOT NULL,
    [MobilePhoneNumber] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Skype] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'FamilyCompSet'
CREATE TABLE [dbo].[FamilyCompSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [SecondName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ChildInfoSet'
CREATE TABLE [dbo].[ChildInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Years] nvarchar(max)  NOT NULL,
    [FamilyCompId] int  NOT NULL
);
GO

-- Creating table 'MilitaryRecordsSet'
CREATE TABLE [dbo].[MilitaryRecordsSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [AppointmentsInfo_Id] int  NOT NULL
);
GO

-- Creating table 'EducationInfoSet'
CREATE TABLE [dbo].[EducationInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Institution] nvarchar(max)  NOT NULL,
    [Specialization] nvarchar(max)  NOT NULL,
    [Faculty] nvarchar(max)  NOT NULL,
    [Classification] nvarchar(max)  NOT NULL,
    [Specialty] nvarchar(max)  NOT NULL,
    [StartDate] nvarchar(max)  NOT NULL,
    [FinishDate] nvarchar(max)  NOT NULL,
    [EmployeeInfoId] int  NOT NULL,
    [ScholasticDegree] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'EmploymentHistoryInfoSet'
CREATE TABLE [dbo].[EmploymentHistoryInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'JobInfoSet'
CREATE TABLE [dbo].[JobInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [OrganizationName] nvarchar(max)  NOT NULL,
    [Experience] nvarchar(max)  NOT NULL,
    [EmploymentHistoryInfoId] int  NOT NULL
);
GO

-- Creating table 'AppointmentsInfoSet'
CREATE TABLE [dbo].[AppointmentsInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CurrentPosition] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'HistoryAppointmentsInfoSet'
CREATE TABLE [dbo].[HistoryAppointmentsInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Position] nvarchar(max)  NOT NULL,
    [Department] nvarchar(max)  NOT NULL,
    [AppointmentsInfoId] int  NOT NULL
);
GO

-- Creating table 'VacationInfoSet'
CREATE TABLE [dbo].[VacationInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UnusedDays] nvarchar(max)  NOT NULL,
    [UsedDays] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'HistoryVactionInfoSet'
CREATE TABLE [dbo].[HistoryVactionInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FromDate] nvarchar(max)  NOT NULL,
    [ToDate] nvarchar(max)  NOT NULL,
    [VacationInfoId] int  NOT NULL
);
GO

-- Creating table 'ContractsInfoSet'
CREATE TABLE [dbo].[ContractsInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [File] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'HistoryContractInfoSet'
CREATE TABLE [dbo].[HistoryContractInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [File] nvarchar(max)  NOT NULL,
    [ContractsInfoId] int  NOT NULL
);
GO

-- Creating table 'HospitalInfoSet'
CREATE TABLE [dbo].[HospitalInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UnusedDays] nvarchar(max)  NOT NULL,
    [UsedDays] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'HistoryHospitalInfoSet'
CREATE TABLE [dbo].[HistoryHospitalInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FromDate] nvarchar(max)  NOT NULL,
    [ToDate] nvarchar(max)  NOT NULL,
    [HospitalInfoId] int  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [ID] bigint IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(255)  NOT NULL,
    [Password] nvarchar(255)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [PK_EmployeeInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PassportInfoSet'
ALTER TABLE [dbo].[PassportInfoSet]
ADD CONSTRAINT [PK_PassportInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ContactInfoSet'
ALTER TABLE [dbo].[ContactInfoSet]
ADD CONSTRAINT [PK_ContactInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FamilyCompSet'
ALTER TABLE [dbo].[FamilyCompSet]
ADD CONSTRAINT [PK_FamilyCompSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ChildInfoSet'
ALTER TABLE [dbo].[ChildInfoSet]
ADD CONSTRAINT [PK_ChildInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MilitaryRecordsSet'
ALTER TABLE [dbo].[MilitaryRecordsSet]
ADD CONSTRAINT [PK_MilitaryRecordsSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EducationInfoSet'
ALTER TABLE [dbo].[EducationInfoSet]
ADD CONSTRAINT [PK_EducationInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EmploymentHistoryInfoSet'
ALTER TABLE [dbo].[EmploymentHistoryInfoSet]
ADD CONSTRAINT [PK_EmploymentHistoryInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'JobInfoSet'
ALTER TABLE [dbo].[JobInfoSet]
ADD CONSTRAINT [PK_JobInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AppointmentsInfoSet'
ALTER TABLE [dbo].[AppointmentsInfoSet]
ADD CONSTRAINT [PK_AppointmentsInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HistoryAppointmentsInfoSet'
ALTER TABLE [dbo].[HistoryAppointmentsInfoSet]
ADD CONSTRAINT [PK_HistoryAppointmentsInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'VacationInfoSet'
ALTER TABLE [dbo].[VacationInfoSet]
ADD CONSTRAINT [PK_VacationInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HistoryVactionInfoSet'
ALTER TABLE [dbo].[HistoryVactionInfoSet]
ADD CONSTRAINT [PK_HistoryVactionInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ContractsInfoSet'
ALTER TABLE [dbo].[ContractsInfoSet]
ADD CONSTRAINT [PK_ContractsInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HistoryContractInfoSet'
ALTER TABLE [dbo].[HistoryContractInfoSet]
ADD CONSTRAINT [PK_HistoryContractInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HospitalInfoSet'
ALTER TABLE [dbo].[HospitalInfoSet]
ADD CONSTRAINT [PK_HospitalInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HistoryHospitalInfoSet'
ALTER TABLE [dbo].[HistoryHospitalInfoSet]
ADD CONSTRAINT [PK_HistoryHospitalInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [ID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PassportInfo_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoPassportInfo]
    FOREIGN KEY ([PassportInfo_Id])
    REFERENCES [dbo].[PassportInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoPassportInfo'
CREATE INDEX [IX_FK_EmployeeInfoPassportInfo]
ON [dbo].[EmployeeInfoSet]
    ([PassportInfo_Id]);
GO

-- Creating foreign key on [FamilyComp_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoFamilyComp]
    FOREIGN KEY ([FamilyComp_Id])
    REFERENCES [dbo].[FamilyCompSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoFamilyComp'
CREATE INDEX [IX_FK_EmployeeInfoFamilyComp]
ON [dbo].[EmployeeInfoSet]
    ([FamilyComp_Id]);
GO

-- Creating foreign key on [MilitaryRecords_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoMilitaryRecords]
    FOREIGN KEY ([MilitaryRecords_Id])
    REFERENCES [dbo].[MilitaryRecordsSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoMilitaryRecords'
CREATE INDEX [IX_FK_EmployeeInfoMilitaryRecords]
ON [dbo].[EmployeeInfoSet]
    ([MilitaryRecords_Id]);
GO

-- Creating foreign key on [EmployeeInfoId] in table 'EducationInfoSet'
ALTER TABLE [dbo].[EducationInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoEducationInfo]
    FOREIGN KEY ([EmployeeInfoId])
    REFERENCES [dbo].[EmployeeInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoEducationInfo'
CREATE INDEX [IX_FK_EmployeeInfoEducationInfo]
ON [dbo].[EducationInfoSet]
    ([EmployeeInfoId]);
GO

-- Creating foreign key on [EmploymentHistoryInfo_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoEmploymentHistoryInfo]
    FOREIGN KEY ([EmploymentHistoryInfo_Id])
    REFERENCES [dbo].[EmploymentHistoryInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoEmploymentHistoryInfo'
CREATE INDEX [IX_FK_EmployeeInfoEmploymentHistoryInfo]
ON [dbo].[EmployeeInfoSet]
    ([EmploymentHistoryInfo_Id]);
GO

-- Creating foreign key on [VacationInfo_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoVacationInfo]
    FOREIGN KEY ([VacationInfo_Id])
    REFERENCES [dbo].[VacationInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoVacationInfo'
CREATE INDEX [IX_FK_EmployeeInfoVacationInfo]
ON [dbo].[EmployeeInfoSet]
    ([VacationInfo_Id]);
GO

-- Creating foreign key on [ContactInfo_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoContactInfo1]
    FOREIGN KEY ([ContactInfo_Id])
    REFERENCES [dbo].[ContactInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoContactInfo1'
CREATE INDEX [IX_FK_EmployeeInfoContactInfo1]
ON [dbo].[EmployeeInfoSet]
    ([ContactInfo_Id]);
GO

-- Creating foreign key on [HospitalInfo_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoHospitalInfo]
    FOREIGN KEY ([HospitalInfo_Id])
    REFERENCES [dbo].[HospitalInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoHospitalInfo'
CREATE INDEX [IX_FK_EmployeeInfoHospitalInfo]
ON [dbo].[EmployeeInfoSet]
    ([HospitalInfo_Id]);
GO

-- Creating foreign key on [ContractsInfo_Id] in table 'EmployeeInfoSet'
ALTER TABLE [dbo].[EmployeeInfoSet]
ADD CONSTRAINT [FK_EmployeeInfoContractsInfo]
    FOREIGN KEY ([ContractsInfo_Id])
    REFERENCES [dbo].[ContractsInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeInfoContractsInfo'
CREATE INDEX [IX_FK_EmployeeInfoContractsInfo]
ON [dbo].[EmployeeInfoSet]
    ([ContractsInfo_Id]);
GO

-- Creating foreign key on [FamilyCompId] in table 'ChildInfoSet'
ALTER TABLE [dbo].[ChildInfoSet]
ADD CONSTRAINT [FK_FamilyCompChildInfo]
    FOREIGN KEY ([FamilyCompId])
    REFERENCES [dbo].[FamilyCompSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FamilyCompChildInfo'
CREATE INDEX [IX_FK_FamilyCompChildInfo]
ON [dbo].[ChildInfoSet]
    ([FamilyCompId]);
GO

-- Creating foreign key on [EmploymentHistoryInfoId] in table 'JobInfoSet'
ALTER TABLE [dbo].[JobInfoSet]
ADD CONSTRAINT [FK_EmploymentHistoryInfoJobInfo]
    FOREIGN KEY ([EmploymentHistoryInfoId])
    REFERENCES [dbo].[EmploymentHistoryInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmploymentHistoryInfoJobInfo'
CREATE INDEX [IX_FK_EmploymentHistoryInfoJobInfo]
ON [dbo].[JobInfoSet]
    ([EmploymentHistoryInfoId]);
GO

-- Creating foreign key on [AppointmentsInfoId] in table 'HistoryAppointmentsInfoSet'
ALTER TABLE [dbo].[HistoryAppointmentsInfoSet]
ADD CONSTRAINT [FK_AppointmentsInfoHistoryAppointmentsInfo]
    FOREIGN KEY ([AppointmentsInfoId])
    REFERENCES [dbo].[AppointmentsInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AppointmentsInfoHistoryAppointmentsInfo'
CREATE INDEX [IX_FK_AppointmentsInfoHistoryAppointmentsInfo]
ON [dbo].[HistoryAppointmentsInfoSet]
    ([AppointmentsInfoId]);
GO

-- Creating foreign key on [AppointmentsInfo_Id] in table 'MilitaryRecordsSet'
ALTER TABLE [dbo].[MilitaryRecordsSet]
ADD CONSTRAINT [FK_MilitaryRecordsAppointmentsInfo]
    FOREIGN KEY ([AppointmentsInfo_Id])
    REFERENCES [dbo].[AppointmentsInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MilitaryRecordsAppointmentsInfo'
CREATE INDEX [IX_FK_MilitaryRecordsAppointmentsInfo]
ON [dbo].[MilitaryRecordsSet]
    ([AppointmentsInfo_Id]);
GO

-- Creating foreign key on [VacationInfoId] in table 'HistoryVactionInfoSet'
ALTER TABLE [dbo].[HistoryVactionInfoSet]
ADD CONSTRAINT [FK_VacationInfoHistoryVactionInfo]
    FOREIGN KEY ([VacationInfoId])
    REFERENCES [dbo].[VacationInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VacationInfoHistoryVactionInfo'
CREATE INDEX [IX_FK_VacationInfoHistoryVactionInfo]
ON [dbo].[HistoryVactionInfoSet]
    ([VacationInfoId]);
GO

-- Creating foreign key on [ContractsInfoId] in table 'HistoryContractInfoSet'
ALTER TABLE [dbo].[HistoryContractInfoSet]
ADD CONSTRAINT [FK_ContractsInfoHistoryContractInfo]
    FOREIGN KEY ([ContractsInfoId])
    REFERENCES [dbo].[ContractsInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContractsInfoHistoryContractInfo'
CREATE INDEX [IX_FK_ContractsInfoHistoryContractInfo]
ON [dbo].[HistoryContractInfoSet]
    ([ContractsInfoId]);
GO

-- Creating foreign key on [HospitalInfoId] in table 'HistoryHospitalInfoSet'
ALTER TABLE [dbo].[HistoryHospitalInfoSet]
ADD CONSTRAINT [FK_HospitalInfoHistoryHospitalInfo]
    FOREIGN KEY ([HospitalInfoId])
    REFERENCES [dbo].[HospitalInfoSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HospitalInfoHistoryHospitalInfo'
CREATE INDEX [IX_FK_HospitalInfoHistoryHospitalInfo]
ON [dbo].[HistoryHospitalInfoSet]
    ([HospitalInfoId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------